package mytest.wd3;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class Test_1 {

    public static void main(String[] args) {
        String sql1 = "select dial_task_gid,user_gid \n" +
                "  from log_dial_task_assign_%s \n" +
                "  where (end_time is null and create_time <= 1728082799 ) or (create_time <= 1728082799  and end_time >= 1727996400) ";

        String sql2 = "select task_id,user_classify,business_id,cus_cat from t_dial_task_%s";

        String sql3 = "select task_id,user_id,call_length\n" +
                "    from t_call_log_%s \n" +
                "  where start_time >= 1727996400000 and start_time <= 1728082799000";
        int n = 5;
        String dateStr = "2024-09-30";
        DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE;
        DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");
        LocalDate date = LocalDate.parse(dateStr, formatter);
        LocalDate yesterday = LocalDate.now().minusDays(1);

        for (int i = 0; i < n; i++) {
            String previousDay = date.minusMonths(i).format(formatter);
            LocalDate datePrevious = LocalDate.parse(previousDay, formatter);
            String previousMonth = date.minusMonths(i).format(formatter2);
            String s1 = String.format(sql1, previousMonth);
            System.out.println(s1);
            System.out.println(datePrevious);

            StringBuilder s2 = new StringBuilder();
            for (int j = 0; j < n; j++) {
                String previousMonth2 = datePrevious.minusMonths(j).format(formatter2);
                if (j != n - 1) {
                    s2.append(String.format(sql2, previousMonth2)).append("\nunion all\n");
                } else{
                    s2.append(String.format(sql2, previousMonth2));
                }
            }
            System.out.println(s2);

            LocalDate startDate = datePrevious.withDayOfMonth(1);
            LocalDate endDate = yesterday.withDayOfMonth(1);
            StringBuilder s3 = new StringBuilder();
            while (!startDate.isAfter(endDate)) {
                String currentMonth = startDate.format(formatter2);
                s3.append(String.format(sql3, currentMonth));
                if (!startDate.equals(endDate)) {
                    s3.append("\nunion all\n");
                }
                startDate = startDate.plusMonths(1);
            }
            System.out.println(s3);

            System.out.println("=========================================");
        }
    }
}
